#-*- coding: UTF-8 -*-
import time
import threading
import subprocess
from queue import Queue
from concurrent.futures import ThreadPoolExecutor, wait, ALL_COMPLETED
import platform
import pymysql

class mysql_io:
    def __init__(self,host,user,passwd,db_name):
        self.host=host
        self.port=3306
        self.user=user
        self.password=passwd
        self.db_name=db_name

    #查询一条
    def select_one(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        cursor.execute(sql)
        data=cursor.fetchone()
        db.close()
        return data
    #查询多条
    def select_all(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        cursor.execute(sql)
        data=cursor.fetchall()
        db.close()
        return data

    #更新数据
    def update(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return "更新成功"
        except:
            db.rollback()
        db.close()

    def insert(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return "插入成功"
        except:
            db.rollback()
        db.close()


def ping_ip(ip):
        os_type=platform.system()
        if os_type=='Windows':
            res = subprocess.call('ping -n 2 -w 5 %s' % ip,stdout=subprocess.PIPE)  # linux 系统将 '-n' 替换成 '-c',增加 'shell=True'
        else:
            res = subprocess.call('ping -c 2 -w 5 %s' % ip,shell=True,stdout=subprocess.PIPE)  # linux 系统将 '-n' 替换成 '-c',增加 'shell=True'
        # windows 系统，则使用 'ping -n 2 -w 5 $s'
        # 打印运行结果
        sql='update ipdb.tp_ipdb set on_off_status=%s where ip="%s";'%(res,ip[0])
        print (sql)
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        ip_list=my.update(sql)

def all_if_pc_off():
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        ip_list=my.select_all("select ip from ipdb.tp_ipdb;")
        IP_QUEUE = Queue()
        for addr in ip_list:
            IP_QUEUE.put(addr)
        pool = ThreadPoolExecutor(max_workers=20)
        lock = threading.Lock()
        start_time = time.time()
        all_task = []
        while not IP_QUEUE.empty():
            all_task.append(pool.submit(ping_ip, IP_QUEUE.get()))
        # 等待所有任务结束
        wait(all_task, return_when=ALL_COMPLETED)
        print('ping耗时：%s' % (time.time() - start_time))
        # ip_list = get_ip_list()
        # ip_list.sort()

host="222.222.222.2"
user="root"
dbname="ipdb"
passwd="123456"

all_if_pc_off()

